package com.abhipedro.wordsearch.model.dictionary;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;

import org.apache.http.util.ByteArrayBuffer;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.abhipedro.wordsearch.view.R;

/**
 * @author abhi
 * Stores all the words from raw langauge files to database. 
 * Provides list of words for given language.
 */
public class WordsDbCreator {

	private Context mContext;
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	private final String DB_NAME = "wordsearch.sqlite";
	private final int DB_VERSION = 9;
	private final String TABLE_ROW_ID = "id";
	private final String TABLE_ROW_ONE_NAME = "val";
	
	public WordsDbCreator(Context context) {
		this.mContext = context;
	}

	public WordsDbCreator open() {
		mDbHelper = new DatabaseHelper(mContext);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public boolean isOpen() {
		if (mDb == null)
			return false;
		if (!mDb.isOpen())
			return false;
		else
			return true;
	}

	public void close() {
		mDbHelper.close();
		mDb.close();
	}
	

	/**
	 *  Return 50 random letters for given language
	 * @param TABLE_NAME - table name for given language
	 */
	public ArrayList<String> getWordsForLang(String TABLE_NAME) {
		ArrayList<String> dataArrays = new ArrayList<String>();
		Cursor cursor = null;
		try {
			cursor = mDb.query(TABLE_NAME, new String[] { TABLE_ROW_ID,
					TABLE_ROW_ONE_NAME, }, null, null, null, null, "RANDOM() LIMIT 50");

			cursor.moveToFirst();
			if (!cursor.isAfterLast()) {
				do {
					dataArrays.add(cursor.getString(1));
				} while (cursor.moveToNext());
			}
		} catch (SQLException e) {
			Log.e("DB Error", e.toString());
			e.printStackTrace();
		} finally {
			if (null != cursor)
				cursor.close();
		}
		
		return dataArrays;
	}
	
	private class DatabaseHelper extends SQLiteOpenHelper {
		
		public DatabaseHelper(Context context) {
			super(context, DB_NAME, null, DB_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			initDatabase(db);
		}

		/**
		 * Populates the database for each language
		 */
		private void initDatabase(SQLiteDatabase db) {
			initTable(mContext.getString(R.string.ENGLISH) ,R.raw.english, db);
			initTable(mContext.getString(R.string.FRENCH) ,R.raw.french, db);
			initTable(mContext.getString(R.string.GERMAN) ,R.raw.german, db);
			initTable(mContext.getString(R.string.ITALIAN) ,R.raw.italian, db);
			initTable(mContext.getString(R.string.PORTUGUESE) ,R.raw.portuguese, db);
			initTable(mContext.getString(R.string.SPANISH) ,R.raw.spanish, db);
			initTable(mContext.getString(R.string.DUTCH) ,R.raw.dutch, db);
			initTable(mContext.getString(R.string.SWEDISH) ,R.raw.swedish, db);
			
		}

		public boolean initTable(String tablename, int resourceId,
				SQLiteDatabase db) {
			
			InputStream is = mContext.getResources().openRawResource(resourceId);
			BufferedInputStream bis = new BufferedInputStream(is);

			ByteArrayBuffer baf = new ByteArrayBuffer(10000);

			int current = 0;

			try {
				while ((current = bis.read()) != -1) {
					baf.append((byte) current);
				}
			} catch (IOException e) {
				e.printStackTrace();
			}

			byte[] myData = baf.toByteArray();
			String dataInString = new String(myData);
			String[] words = dataInString.split("\\n");

			try {
				is.close();
			} catch (IOException e) {
				e.printStackTrace();
			}

			String query = "create table " + tablename + " (" + TABLE_ROW_ID
					+ " integer primary key autoincrement not null,"
					+ TABLE_ROW_ONE_NAME + " text" + ");";

			db.execSQL(query);

			for (String s : words) {
				ContentValues vals = new ContentValues();
				vals.put(TABLE_ROW_ONE_NAME, s.trim());

				try {
					db.insert(tablename, null, vals);
				} catch (Exception e) {
					Log.e("DB ERROR", e.toString());
					e.printStackTrace();
				}
			}

			return true;
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			dropTables(db);
			initDatabase(db);
		}
		
		private void dropTables(SQLiteDatabase db) {
			db.execSQL("DROP TABLE IF EXISTS " + mContext.getString(R.string.ENGLISH));
			db.execSQL("DROP TABLE IF EXISTS "	+ mContext.getString(R.string.FRENCH));
			db.execSQL("DROP TABLE IF EXISTS "	+ mContext.getString(R.string.GERMAN));
			db.execSQL("DROP TABLE IF EXISTS "	+ mContext.getString(R.string.ITALIAN));
			db.execSQL("DROP TABLE IF EXISTS "	+ mContext.getString(R.string.PORTUGUESE));
			db.execSQL("DROP TABLE IF EXISTS "	+ mContext.getString(R.string.SPANISH));
			
		}
	}
}